/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80026
 Source Host           : localhost:3306
 Source Schema         : smart_agri

 Target Server Type    : MySQL
 Target Server Version : 80026
 File Encoding         : 65001

 Date: 10/03/2025 01:22:48
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for complaint
-- ----------------------------
DROP TABLE IF EXISTS `complaint`;
CREATE TABLE `complaint` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '投诉ID',
  `buyer_id` bigint NOT NULL COMMENT '买家ID',
  `seller_id` bigint NOT NULL COMMENT '卖家/农户ID',
  `order_id` bigint NOT NULL COMMENT '订单ID',
  `order_number` varchar(50) NOT NULL COMMENT '订单编号',
  `product_name` varchar(100) NOT NULL COMMENT '商品名称',
  `title` varchar(100) NOT NULL COMMENT '投诉标题',
  `type` varchar(20) NOT NULL COMMENT '投诉类型(quality:商品质量问题, delivery:物流配送问题, service:售后服务问题, description:商品描述不符, other:其他问题)',
  `content` text NOT NULL COMMENT '投诉内容',
  `images` text COMMENT '投诉图片，JSON数组格式',
  `phone` varchar(20) NOT NULL COMMENT '联系电话',
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT '投诉状态(pending:待处理, processing:处理中, resolved:已解决, closed:已关闭)',
  `reply_content` text COMMENT '商家回复内容',
  `reply_time` datetime DEFAULT NULL COMMENT '商家回复时间',
  `solution` text COMMENT '解决方案',
  `resolve_time` datetime DEFAULT NULL COMMENT '解决时间',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`),
  KEY `idx_buyer_id` (`buyer_id`),
  KEY `idx_seller_id` (`seller_id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='投诉表';

-- ----------------------------
-- Records of complaint
-- ----------------------------
BEGIN;
INSERT INTO `complaint` (`id`, `buyer_id`, `seller_id`, `order_id`, `order_number`, `product_name`, `title`, `type`, `content`, `images`, `phone`, `status`, `reply_content`, `reply_time`, `solution`, `resolve_time`, `create_time`, `update_time`, `deleted`) VALUES (2, 18, 16, 26, '20250309222358353884', '有机番茄', '收到的番茄有部分已腐烂', '产品质量问题', '订购的10斤有机番茄，收到后发现有约2斤番茄已经开始腐烂，希望能得到退款或换货。', NULL, '13800138000', 'pending', NULL, NULL, NULL, NULL, '2025-03-09 22:31:50', '2025-03-09 22:31:50', 0);
COMMIT;

-- ----------------------------
-- Table structure for fertilizing_record
-- ----------------------------
DROP TABLE IF EXISTS `fertilizing_record`;
CREATE TABLE `fertilizing_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `farmer_id` bigint NOT NULL COMMENT '农户ID',
  `land_id` bigint NOT NULL COMMENT '土地ID',
  `crop_type` varchar(50) DEFAULT NULL COMMENT '作物类型',
  `fertilizer_id` bigint NOT NULL COMMENT '肥料ID',
  `date` datetime DEFAULT NULL COMMENT '施肥日期',
  `amount` decimal(10,2) DEFAULT NULL COMMENT '用量(kg)',
  `note` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint DEFAULT '0' COMMENT '是否删除(0未删除,1已删除)',
  PRIMARY KEY (`id`),
  KEY `idx_farmer_id` (`farmer_id`),
  KEY `idx_land_id` (`land_id`),
  KEY `idx_fertilizer_id` (`fertilizer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='施肥记录表';

-- ----------------------------
-- Records of fertilizing_record
-- ----------------------------
BEGIN;
INSERT INTO `fertilizing_record` (`id`, `farmer_id`, `land_id`, `crop_type`, `fertilizer_id`, `date`, `amount`, `note`, `create_time`, `update_time`, `deleted`) VALUES (11, 16, 23, '生菜', 17, '2025-03-10 00:00:00', 7.00, '更新施肥量为7包', '2025-03-10 01:01:04', '2025-03-10 01:04:12', 1);
INSERT INTO `fertilizing_record` (`id`, `farmer_id`, `land_id`, `crop_type`, `fertilizer_id`, `date`, `amount`, `note`, `create_time`, `update_time`, `deleted`) VALUES (12, 16, 23, '生菜', 17, '2025-03-10 00:00:00', 1.00, '', '2025-03-10 01:19:22', '2025-03-10 01:19:22', 0);
COMMIT;

-- ----------------------------
-- Table structure for inventory
-- ----------------------------
DROP TABLE IF EXISTS `inventory`;
CREATE TABLE `inventory` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '库存ID',
  `farmer_id` bigint NOT NULL COMMENT '农户ID',
  `name` varchar(50) NOT NULL COMMENT '物品名称',
  `type` varchar(20) NOT NULL COMMENT '物品类型：种子、肥料、农药、工具、农产品、其他',
  `item_id` bigint DEFAULT NULL COMMENT '关联ID (可关联到具体物品表，如肥料ID)',
  `quantity` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '数量',
  `unit` varchar(10) NOT NULL COMMENT '单位 (kg, 袋, 瓶等)',
  `price` decimal(10,2) DEFAULT '0.00' COMMENT '单价',
  `date` datetime NOT NULL COMMENT '入库日期',
  `production_date` datetime DEFAULT NULL COMMENT '生产日期',
  `expiry_date` datetime DEFAULT NULL COMMENT '过期日期',
  `alert_threshold` decimal(10,2) DEFAULT NULL COMMENT '预警阈值',
  `note` varchar(255) DEFAULT NULL COMMENT '备注',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除(0未删除,1已删除)',
  PRIMARY KEY (`id`),
  KEY `idx_farmer_id` (`farmer_id`),
  KEY `idx_type` (`type`),
  KEY `idx_item_id` (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='通用库存表';

-- ----------------------------
-- Records of inventory
-- ----------------------------
BEGIN;
INSERT INTO `inventory` (`id`, `farmer_id`, `name`, `type`, `item_id`, `quantity`, `unit`, `price`, `date`, `production_date`, `expiry_date`, `alert_threshold`, `note`, `create_time`, `update_time`, `deleted`) VALUES (17, 16, '测试肥料', '肥料', NULL, 100.00, '包', 0.00, '2025-03-10 00:00:00', NULL, NULL, 10.00, '', '2025-03-10 00:05:17', '2025-03-10 00:05:17', 0);
COMMIT;

-- ----------------------------
-- Table structure for land
-- ----------------------------
DROP TABLE IF EXISTS `land`;
CREATE TABLE `land` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '土地ID',
  `farmer_id` bigint NOT NULL COMMENT '农户ID',
  `name` varchar(100) NOT NULL COMMENT '土地名称',
  `location` varchar(255) NOT NULL COMMENT '土地位置',
  `area` decimal(10,2) NOT NULL COMMENT '土地面积(亩)',
  `type` tinyint NOT NULL COMMENT '土地类型(1:水田, 2:旱地, 3:果园, 4:菜地, 5:其他)',
  `soil_type` tinyint NOT NULL COMMENT '土壤类型(1:砂质土, 2:粘土, 3:壤土, 4:石灰土, 5:其他)',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT '土地状态(0:闲置, 1:已种植, 2:已收获)',
  `current_crop` varchar(100) DEFAULT NULL COMMENT '当前种植作物',
  `planting_date` datetime DEFAULT NULL COMMENT '种植日期',
  `fertility` tinyint DEFAULT NULL COMMENT '土壤肥力(1:低, 2:中, 3:高)',
  `irrigation` tinyint DEFAULT NULL COMMENT '灌溉条件(1:差, 2:一般, 3:好)',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除(0未删除,1已删除)',
  `ph` decimal(4,2) DEFAULT NULL COMMENT '土壤pH值',
  `organic_matter` decimal(5,2) DEFAULT NULL COMMENT '有机质含量(%)',
  `nitrogen` decimal(5,2) DEFAULT NULL COMMENT '氮含量(%)',
  `phosphorus` decimal(5,2) DEFAULT NULL COMMENT '磷含量(%)',
  `potassium` decimal(5,2) DEFAULT NULL COMMENT '钾含量(%)',
  `image` mediumtext,
  PRIMARY KEY (`id`),
  KEY `idx_farmer_id` (`farmer_id`),
  KEY `idx_status` (`status`),
  KEY `idx_type` (`type`),
  KEY `idx_soil_type` (`soil_type`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='土地信息表';

-- ----------------------------
-- Records of land
-- ----------------------------
BEGIN;
INSERT INTO `land` (`id`, `farmer_id`, `name`, `location`, `area`, `type`, `soil_type`, `status`, `current_crop`, `planting_date`, `fertility`, `irrigation`, `remark`, `create_time`, `update_time`, `deleted`, `ph`, `organic_matter`, `nitrogen`, `phosphorus`, `potassium`, `image`) VALUES (20, 16, '水稻田', '江苏省苏州市吴江区', 5000.00, 1, 3, 1, '水稻', '2024-03-09 10:00:00', 3, 3, '优质水稻种植基地', '2025-03-09 21:56:42', '2025-03-09 21:56:42', 0, 6.50, 2.50, 0.15, 0.10, 0.20, NULL);
INSERT INTO `land` (`id`, `farmer_id`, `name`, `location`, `area`, `type`, `soil_type`, `status`, `current_crop`, `planting_date`, `fertility`, `irrigation`, `remark`, `create_time`, `update_time`, `deleted`, `ph`, `organic_matter`, `nitrogen`, `phosphorus`, `potassium`, `image`) VALUES (21, 16, '蔬菜大棚', '江苏省苏州市吴江区', 2000.00, 4, 2, 1, '生菜', '2024-03-09 10:00:00', 2, 2, '适合种植各类蔬菜', '2025-03-09 21:56:57', '2025-03-09 21:56:57', 0, 7.00, 3.00, 0.20, 0.15, 0.25, NULL);
INSERT INTO `land` (`id`, `farmer_id`, `name`, `location`, `area`, `type`, `soil_type`, `status`, `current_crop`, `planting_date`, `fertility`, `irrigation`, `remark`, `create_time`, `update_time`, `deleted`, `ph`, `organic_matter`, `nitrogen`, `phosphorus`, `potassium`, `image`) VALUES (22, 16, '水稻田1号', '东湖区农业园区A区', 10.50, 1, 3, 1, '水稻', '2024-03-09 00:00:00', 2, 3, '优质水稻种植基地', '2025-03-09 22:38:04', '2025-03-09 22:38:04', 0, 6.50, 2.50, 0.15, 0.12, 0.18, NULL);
INSERT INTO `land` (`id`, `farmer_id`, `name`, `location`, `area`, `type`, `soil_type`, `status`, `current_crop`, `planting_date`, `fertility`, `irrigation`, `remark`, `create_time`, `update_time`, `deleted`, `ph`, `organic_matter`, `nitrogen`, `phosphorus`, `potassium`, `image`) VALUES (23, 16, '蔬菜地1号', '东湖区农业园区B区', 5.00, 4, 3, 1, '生菜', '2024-03-09 00:00:00', 3, 3, '有机蔬菜种植基地', '2025-03-09 22:38:15', '2025-03-09 22:38:15', 0, 7.00, 3.00, 0.20, 0.15, 0.20, NULL);
COMMIT;

-- ----------------------------
-- Table structure for land_crop_record
-- ----------------------------
DROP TABLE IF EXISTS `land_crop_record`;
CREATE TABLE `land_crop_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `land_id` bigint NOT NULL COMMENT '土地ID',
  `farmer_id` bigint NOT NULL COMMENT '农户ID',
  `crop_name` varchar(100) NOT NULL COMMENT '作物名称',
  `crop_variety` varchar(100) DEFAULT NULL COMMENT '作物品种',
  `planting_area` decimal(10,2) NOT NULL COMMENT '种植面积(亩)',
  `planting_date` datetime NOT NULL COMMENT '种植日期',
  `harvest_date` datetime DEFAULT NULL COMMENT '收获日期',
  `expected_yield` decimal(10,2) DEFAULT NULL COMMENT '预计产量(斤)',
  `actual_yield` decimal(10,2) DEFAULT NULL COMMENT '实际产量(斤)',
  `yield_per_unit` decimal(10,2) DEFAULT NULL COMMENT '产量单位(斤/亩)',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0:种植中, 1:已收获, 2:已作废)',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除(0未删除,1已删除)',
  PRIMARY KEY (`id`),
  KEY `idx_land_id` (`land_id`),
  KEY `idx_farmer_id` (`farmer_id`),
  KEY `idx_crop_name` (`crop_name`),
  KEY `idx_status` (`status`),
  KEY `idx_planting_date` (`planting_date`),
  KEY `idx_harvest_date` (`harvest_date`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='土地作物记录表';

-- ----------------------------
-- Records of land_crop_record
-- ----------------------------
BEGIN;
INSERT INTO `land_crop_record` (`id`, `land_id`, `farmer_id`, `crop_name`, `crop_variety`, `planting_area`, `planting_date`, `harvest_date`, `expected_yield`, `actual_yield`, `yield_per_unit`, `status`, `remark`, `create_time`, `update_time`, `deleted`) VALUES (37, 20, 16, '水稻', '软香粳', 5000.00, '2024-03-09 10:00:00', '2024-09-09 10:00:00', 25000.00, NULL, 5.00, 0, '第一季水稻种植', '2025-03-09 21:58:51', '2025-03-09 21:58:51', 0);
INSERT INTO `land_crop_record` (`id`, `land_id`, `farmer_id`, `crop_name`, `crop_variety`, `planting_area`, `planting_date`, `harvest_date`, `expected_yield`, `actual_yield`, `yield_per_unit`, `status`, `remark`, `create_time`, `update_time`, `deleted`) VALUES (38, 21, 16, '生菜', '美生菜', 2000.00, '2024-03-09 10:00:00', '2024-04-23 10:00:00', 10000.00, NULL, 5.00, 0, '春季生菜种植', '2025-03-09 21:59:03', '2025-03-09 21:59:03', 0);
INSERT INTO `land_crop_record` (`id`, `land_id`, `farmer_id`, `crop_name`, `crop_variety`, `planting_area`, `planting_date`, `harvest_date`, `expected_yield`, `actual_yield`, `yield_per_unit`, `status`, `remark`, `create_time`, `update_time`, `deleted`) VALUES (39, 22, 16, '水稻', '秋田小町', 10.50, '2024-03-09 00:00:00', '2024-09-09 00:00:00', 10500.00, NULL, 1000.00, 0, '预计单产1000斤/亩', '2025-03-09 22:38:41', '2025-03-09 22:38:41', 0);
INSERT INTO `land_crop_record` (`id`, `land_id`, `farmer_id`, `crop_name`, `crop_variety`, `planting_area`, `planting_date`, `harvest_date`, `expected_yield`, `actual_yield`, `yield_per_unit`, `status`, `remark`, `create_time`, `update_time`, `deleted`) VALUES (40, 23, 16, '生菜', '油麦菜', 5.00, '2024-03-09 00:00:00', '2024-04-23 00:00:00', 2500.00, NULL, 500.00, 0, '预计单产500斤/亩', '2025-03-09 22:38:51', '2025-03-09 22:38:51', 0);
COMMIT;

-- ----------------------------
-- Table structure for notification
-- ----------------------------
DROP TABLE IF EXISTS `notification`;
CREATE TABLE `notification` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '通知ID',
  `user_id` bigint NOT NULL COMMENT '接收用户ID',
  `title` varchar(100) NOT NULL COMMENT '通知标题',
  `content` varchar(500) NOT NULL COMMENT '通知内容',
  `type` varchar(20) NOT NULL COMMENT '通知类型(system/order/warning等)',
  `is_read` tinyint(1) DEFAULT '0' COMMENT '是否已读',
  `action_route` varchar(100) DEFAULT NULL COMMENT '操作路由',
  `action_text` varchar(50) DEFAULT NULL COMMENT '操作按钮文本',
  `action_type` varchar(20) DEFAULT NULL COMMENT '操作按钮类型',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `read_time` datetime DEFAULT NULL COMMENT '阅读时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_is_read` (`is_read`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统通知表';

-- ----------------------------
-- Records of notification
-- ----------------------------
BEGIN;
INSERT INTO `notification` (`id`, `user_id`, `title`, `content`, `type`, `is_read`, `action_route`, `action_text`, `action_type`, `create_time`, `read_time`, `deleted`) VALUES (31, 16, '新订单通知', 'buyer1已确认订单，请注意发货时间。订单号：20250309220609685878', 'order', 0, '/main/seller/seller-orders', '查看订单', 'primary', '2025-03-09 22:06:21', NULL, 0);
INSERT INTO `notification` (`id`, `user_id`, `title`, `content`, `type`, `is_read`, `action_route`, `action_text`, `action_type`, `create_time`, `read_time`, `deleted`) VALUES (32, 17, '新订单通知', 'farmer1已确认订单，请注意发货时间。订单号：20250309222017585600', 'order', 0, '/main/seller/seller-orders', '查看订单', 'primary', '2025-03-09 22:20:41', NULL, 0);
INSERT INTO `notification` (`id`, `user_id`, `title`, `content`, `type`, `is_read`, `action_route`, `action_text`, `action_type`, `create_time`, `read_time`, `deleted`) VALUES (33, 16, '新订单通知', 'buyer1已确认订单，请注意发货时间。订单号：20250309222358353884', 'order', 0, '/main/seller/seller-orders', '查看订单', 'primary', '2025-03-09 22:24:07', NULL, 0);
INSERT INTO `notification` (`id`, `user_id`, `title`, `content`, `type`, `is_read`, `action_route`, `action_text`, `action_type`, `create_time`, `read_time`, `deleted`) VALUES (34, 18, '订单已发货', 'farmer1已发货，请注意查收。订单号：20250309222358353884，商品：有机番茄', 'order', 0, '/main/buyer/orders', '查看订单', 'primary', '2025-03-09 22:24:47', NULL, 0);
INSERT INTO `notification` (`id`, `user_id`, `title`, `content`, `type`, `is_read`, `action_route`, `action_text`, `action_type`, `create_time`, `read_time`, `deleted`) VALUES (35, 16, '订单已完成', 'buyer1已确认收货，订单交易已完成。订单号：20250309222358353884，商品：有机番茄', 'order', 0, '/main/seller/seller-orders', '查看订单', 'success', '2025-03-09 22:27:31', NULL, 0);
INSERT INTO `notification` (`id`, `user_id`, `title`, `content`, `type`, `is_read`, `action_route`, `action_text`, `action_type`, `create_time`, `read_time`, `deleted`) VALUES (36, 16, '收到新投诉', '您收到一个新的投诉，标题：收到的番茄有部分已腐烂，订单编号：20250309222358353884', 'complaint', 0, '/main/seller/complaints', '查看投诉', 'warning', '2025-03-09 22:31:50', NULL, 0);
COMMIT;

-- ----------------------------
-- Table structure for order_item
-- ----------------------------
DROP TABLE IF EXISTS `order_item`;
CREATE TABLE `order_item` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `order_id` bigint NOT NULL COMMENT '订单ID',
  `product_id` bigint NOT NULL COMMENT '产品ID',
  `product_name` varchar(100) NOT NULL COMMENT '产品名称',
  `price` decimal(10,2) NOT NULL COMMENT '单价',
  `quantity` int NOT NULL COMMENT '数量',
  `subtotal` decimal(10,2) NOT NULL COMMENT '小计',
  `unit` varchar(20) DEFAULT NULL COMMENT '单位',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单项表';

-- ----------------------------
-- Records of order_item
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `order_no` varchar(50) NOT NULL COMMENT '订单编号',
  `buyer_id` bigint NOT NULL COMMENT '买家ID',
  `buyer_name` varchar(50) DEFAULT NULL COMMENT '买家名称',
  `seller_id` bigint NOT NULL COMMENT '卖家ID',
  `seller_name` varchar(50) DEFAULT NULL COMMENT '卖家名称',
  `product_id` bigint NOT NULL COMMENT '产品ID',
  `product_name` varchar(100) DEFAULT NULL COMMENT '产品名称',
  `product_image` varchar(255) DEFAULT NULL COMMENT '产品图片',
  `quantity` decimal(10,2) NOT NULL COMMENT '购买数量',
  `price` decimal(10,2) NOT NULL COMMENT '产品单价',
  `total_amount` decimal(10,2) NOT NULL COMMENT '订单总金额',
  `status` tinyint DEFAULT '0' COMMENT '订单状态：0-待付款，1-待发货，2-待收货，3-已完成，4-已取消',
  `contact_name` varchar(50) DEFAULT NULL COMMENT '联系人',
  `contact_phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `address` varchar(255) DEFAULT NULL COMMENT '收货地址',
  `order_time` datetime DEFAULT NULL COMMENT '下单时间',
  `pay_time` datetime DEFAULT NULL COMMENT '付款时间',
  `ship_time` datetime DEFAULT NULL COMMENT '发货时间',
  `complete_time` datetime DEFAULT NULL COMMENT '完成时间',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注信息',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `order_type` tinyint DEFAULT '0' COMMENT '订单类型：0-农产品订单，1-肥料订单',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_order_no` (`order_no`),
  KEY `idx_buyer_id` (`buyer_id`),
  KEY `idx_seller_id` (`seller_id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表';

-- ----------------------------
-- Records of orders
-- ----------------------------
BEGIN;
INSERT INTO `orders` (`id`, `order_no`, `buyer_id`, `buyer_name`, `seller_id`, `seller_name`, `product_id`, `product_name`, `product_image`, `quantity`, `price`, `total_amount`, `status`, `contact_name`, `contact_phone`, `address`, `order_time`, `pay_time`, `ship_time`, `complete_time`, `remark`, `create_time`, `update_time`, `order_type`) VALUES (22, '20250309220609685878', 18, 'buyer1', 16, NULL, 8, '软香粳大米', NULL, 100.00, 6.00, 600.00, 1, NULL, NULL, NULL, '2025-03-09 22:06:10', '2025-03-09 22:06:21', NULL, NULL, NULL, '2025-03-09 22:06:10', '2025-03-09 22:06:21', 0);
INSERT INTO `orders` (`id`, `order_no`, `buyer_id`, `buyer_name`, `seller_id`, `seller_name`, `product_id`, `product_name`, `product_image`, `quantity`, `price`, `total_amount`, `status`, `contact_name`, `contact_phone`, `address`, `order_time`, `pay_time`, `ship_time`, `complete_time`, `remark`, `create_time`, `update_time`, `order_type`) VALUES (23, '20250309220905421853', 16, 'farmer1', 17, NULL, 10000, '高氮复合肥', NULL, 1.00, 2000.00, 2000.00, 0, NULL, NULL, NULL, '2025-03-09 22:09:05', NULL, NULL, NULL, NULL, '2025-03-09 22:09:05', '2025-03-09 22:09:05', 1);
INSERT INTO `orders` (`id`, `order_no`, `buyer_id`, `buyer_name`, `seller_id`, `seller_name`, `product_id`, `product_name`, `product_image`, `quantity`, `price`, `total_amount`, `status`, `contact_name`, `contact_phone`, `address`, `order_time`, `pay_time`, `ship_time`, `complete_time`, `remark`, `create_time`, `update_time`, `order_type`) VALUES (24, '20250309221702139964', 16, 'farmer1', 17, NULL, 10001, '生物有机肥', NULL, 1.00, 3000.00, 3000.00, 0, NULL, NULL, NULL, '2025-03-09 22:17:02', NULL, NULL, NULL, NULL, '2025-03-09 22:17:02', '2025-03-09 22:17:02', 1);
INSERT INTO `orders` (`id`, `order_no`, `buyer_id`, `buyer_name`, `seller_id`, `seller_name`, `product_id`, `product_name`, `product_image`, `quantity`, `price`, `total_amount`, `status`, `contact_name`, `contact_phone`, `address`, `order_time`, `pay_time`, `ship_time`, `complete_time`, `remark`, `create_time`, `update_time`, `order_type`) VALUES (25, '20250309222017585600', 16, 'farmer1', 17, NULL, 10000, '高氮复合肥', NULL, 1.00, 2000.00, 2000.00, 1, NULL, NULL, NULL, '2025-03-09 22:20:18', '2025-03-09 22:20:41', NULL, NULL, NULL, '2025-03-09 22:20:18', '2025-03-09 22:20:41', 1);
INSERT INTO `orders` (`id`, `order_no`, `buyer_id`, `buyer_name`, `seller_id`, `seller_name`, `product_id`, `product_name`, `product_image`, `quantity`, `price`, `total_amount`, `status`, `contact_name`, `contact_phone`, `address`, `order_time`, `pay_time`, `ship_time`, `complete_time`, `remark`, `create_time`, `update_time`, `order_type`) VALUES (26, '20250309222358353884', 18, 'buyer1', 16, NULL, 10, '有机番茄', NULL, 10.00, 8.00, 80.00, 3, '王买家', '13800138000', '江苏省南京市江宁区学府路1号', '2025-03-09 22:23:58', '2025-03-09 22:24:07', '2025-03-09 22:24:47', '2025-03-09 22:27:31', NULL, '2025-03-09 22:23:58', '2025-03-09 22:27:31', 0);
COMMIT;

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `farmer_id` bigint NOT NULL COMMENT '农户ID',
  `farmer_name` varchar(50) DEFAULT NULL COMMENT '农户名称',
  `name` varchar(100) NOT NULL COMMENT '产品名称',
  `type` varchar(50) DEFAULT NULL COMMENT '产品类型',
  `description` text COMMENT '产品描述',
  `image` varchar(255) DEFAULT NULL COMMENT '产品图片',
  `price` decimal(10,2) NOT NULL COMMENT '产品价格',
  `unit` varchar(20) DEFAULT '斤' COMMENT '计量单位',
  `stock` decimal(10,2) DEFAULT '0.00' COMMENT '库存数量',
  `production_date` datetime DEFAULT NULL COMMENT '生产日期',
  `status` tinyint DEFAULT '0' COMMENT '上架状态：0-下架，1-上架',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注信息',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_farmer_id` (`farmer_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='产品表';

-- ----------------------------
-- Records of product
-- ----------------------------
BEGIN;
INSERT INTO `product` (`id`, `farmer_id`, `farmer_name`, `name`, `type`, `description`, `image`, `price`, `unit`, `stock`, `production_date`, `status`, `remark`, `create_time`, `update_time`) VALUES (8, 16, 'farmer1', '软香粳大米', '粮食', '优质软香粳大米，口感好，营养丰富', NULL, 6.00, '斤', 49900.00, '2024-03-09 00:00:00', 0, '新鲜收割的优质大米', '2025-03-09 22:05:04', '2025-03-09 22:05:04');
INSERT INTO `product` (`id`, `farmer_id`, `farmer_name`, `name`, `type`, `description`, `image`, `price`, `unit`, `stock`, `production_date`, `status`, `remark`, `create_time`, `update_time`) VALUES (9, 16, 'farmer1', '美生菜', '蔬菜', '新鲜美生菜，口感脆嫩，营养丰富', NULL, 3.00, '斤', 10000.00, '2024-03-09 00:00:00', 0, '温室大棚种植，保证新鲜', '2025-03-09 22:05:10', '2025-03-09 22:05:10');
INSERT INTO `product` (`id`, `farmer_id`, `farmer_name`, `name`, `type`, `description`, `image`, `price`, `unit`, `stock`, `production_date`, `status`, `remark`, `create_time`, `update_time`) VALUES (10, 16, 'farmer1', '有机番茄', '蔬菜', '有机种植，不使用化学农药，果实硕大饱满，口感鲜美', NULL, 8.00, '斤', 4990.00, '2024-03-09 00:00:00', 0, '番茄富含维生素C和番茄红素', '2025-03-09 22:23:09', '2025-03-09 22:23:09');
INSERT INTO `product` (`id`, `farmer_id`, `farmer_name`, `name`, `type`, `description`, `image`, `price`, `unit`, `stock`, `production_date`, `status`, `remark`, `create_time`, `update_time`) VALUES (11, 16, 'farmer1', '黄瓜', '蔬菜', '新鲜采摘的黄瓜，脆嫩多汁，清香可口', NULL, 4.00, '斤', 8000.00, '2024-03-09 00:00:00', 0, '脆嫩清香，可做凉拌或炒菜', '2025-03-09 22:23:21', '2025-03-09 22:23:21');
COMMIT;

-- ----------------------------
-- Table structure for seller_product
-- ----------------------------
DROP TABLE IF EXISTS `seller_product`;
CREATE TABLE `seller_product` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `seller_id` bigint NOT NULL COMMENT '卖家ID',
  `seller_name` varchar(50) DEFAULT NULL COMMENT '卖家名称',
  `product_code` varchar(50) DEFAULT NULL COMMENT '商品编号',
  `name` varchar(100) NOT NULL COMMENT '商品名称',
  `category` varchar(50) DEFAULT NULL COMMENT '商品分类',
  `price` decimal(10,2) NOT NULL COMMENT '商品价格',
  `unit` varchar(20) DEFAULT 'kg' COMMENT '计量单位',
  `stock` int DEFAULT '0' COMMENT '库存数量',
  `image` varchar(255) DEFAULT NULL COMMENT '商品图片',
  `description` text COMMENT '商品描述',
  `origin` varchar(100) DEFAULT NULL COMMENT '产地',
  `production_date` datetime DEFAULT NULL COMMENT '生产日期',
  `shelf_life` int DEFAULT '30' COMMENT '保质期(天)',
  `certificates` text COMMENT '品质证书',
  `nutrient_content` varchar(255) DEFAULT NULL COMMENT '养分含量',
  `suitable_crops` text COMMENT '适用作物',
  `instructions` text COMMENT '使用说明',
  `status` tinyint DEFAULT '0' COMMENT '上架状态：0-下架，1-上架',
  `sales` int DEFAULT '0' COMMENT '销量',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注信息',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_seller_id` (`seller_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='卖家商品表';

-- ----------------------------
-- Records of seller_product
-- ----------------------------
BEGIN;
INSERT INTO `seller_product` (`id`, `seller_id`, `seller_name`, `product_code`, `name`, `category`, `price`, `unit`, `stock`, `image`, `description`, `origin`, `production_date`, `shelf_life`, `certificates`, `nutrient_content`, `suitable_crops`, `instructions`, `status`, `sales`, `remark`, `create_time`, `update_time`) VALUES (10000, 17, 'seller1', 'P28862486', '高氮复合肥', '复合肥', 2000.00, '吨', 48, NULL, '含氮量≥25%，适合水稻等粮食作物使用', '江苏省南京市', '2024-03-09 00:00:00', 730, '[\"农业部认证\",\"质量安全认证\"]', 'N≥25%, P≥10%, K≥10%', '[\"水稻\",\"小麦\",\"玉米\"]', NULL, 1, 0, NULL, '2025-03-09 22:01:02', '2025-03-09 22:01:02');
INSERT INTO `seller_product` (`id`, `seller_id`, `seller_name`, `product_code`, `name`, `category`, `price`, `unit`, `stock`, `image`, `description`, `origin`, `production_date`, `shelf_life`, `certificates`, `nutrient_content`, `suitable_crops`, `instructions`, `status`, `sales`, `remark`, `create_time`, `update_time`) VALUES (10001, 17, 'seller1', 'P28875287', '生物有机肥', '有机肥', 3000.00, '吨', 29, NULL, '纯天然有机肥料，富含腐殖酸，改良土壤，提高地力', '江苏省苏州市', '2024-03-09 00:00:00', 365, '[\"有机认证\",\"绿色认证\"]', '有机质≥45%, N+P+K≥5%', '[\"蔬菜\",\"水果\",\"粮食\"]', NULL, 1, 0, NULL, '2025-03-09 22:01:15', '2025-03-09 22:01:15');
INSERT INTO `seller_product` (`id`, `seller_id`, `seller_name`, `product_code`, `name`, `category`, `price`, `unit`, `stock`, `image`, `description`, `origin`, `production_date`, `shelf_life`, `certificates`, `nutrient_content`, `suitable_crops`, `instructions`, `status`, `sales`, `remark`, `create_time`, `update_time`) VALUES (10002, 17, 'seller1', 'P28887970', '螯合锌肥', '微量元素肥', 5000.00, '吨', 20, NULL, '高效螯合锌肥，快速补锌，防治缺锌症状', '江苏省无锡市', '2024-03-09 00:00:00', 730, '[\"农业部认证\",\"质量认证\"]', 'Zn≥15%, 螯合度≥90%', '[\"水稻\",\"玉米\",\"果树\"]', NULL, 1, 0, NULL, '2025-03-09 22:01:28', '2025-03-09 22:01:28');
COMMIT;

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(100) NOT NULL COMMENT '密码',
  `nickname` varchar(50) DEFAULT NULL COMMENT '昵称',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `role` varchar(20) NOT NULL DEFAULT 'buyer' COMMENT '角色(buyer买家, seller卖家, farmer农户, admin管理员)',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态(0禁用,1启用)',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除(0未删除,1已删除)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

-- ----------------------------
-- Records of sys_user
-- ----------------------------
BEGIN;
INSERT INTO `sys_user` (`id`, `username`, `password`, `nickname`, `email`, `phone`, `role`, `status`, `create_time`, `update_time`, `deleted`) VALUES (16, 'farmer1', 'e10adc3949ba59abbe56e057f20f883e', '张农夫', NULL, NULL, 'farmer', 1, '2025-03-09 21:55:02', '2025-03-09 21:55:02', 0);
INSERT INTO `sys_user` (`id`, `username`, `password`, `nickname`, `email`, `phone`, `role`, `status`, `create_time`, `update_time`, `deleted`) VALUES (17, 'seller1', 'e10adc3949ba59abbe56e057f20f883e', '李卖家', NULL, NULL, 'seller', 1, '2025-03-09 21:55:11', '2025-03-09 21:55:11', 0);
INSERT INTO `sys_user` (`id`, `username`, `password`, `nickname`, `email`, `phone`, `role`, `status`, `create_time`, `update_time`, `deleted`) VALUES (18, 'buyer1', 'e10adc3949ba59abbe56e057f20f883e', '王买家', NULL, NULL, 'buyer', 1, '2025-03-09 21:55:19', '2025-03-09 21:55:19', 0);
COMMIT;

-- ----------------------------
-- Table structure for transaction_record
-- ----------------------------
DROP TABLE IF EXISTS `transaction_record`;
CREATE TABLE `transaction_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '交易记录ID',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `type` varchar(20) NOT NULL COMMENT '交易类型(income/expense)',
  `order_number` varchar(50) DEFAULT NULL COMMENT '订单编号',
  `product_name` varchar(100) DEFAULT NULL COMMENT '产品名称',
  `quantity` decimal(10,2) DEFAULT NULL COMMENT '数量',
  `unit` varchar(20) DEFAULT NULL COMMENT '单位',
  `amount` decimal(10,2) NOT NULL COMMENT '金额',
  `transaction_date` datetime NOT NULL COMMENT '交易日期',
  `note` varchar(255) DEFAULT NULL COMMENT '备注',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_transaction_date` (`transaction_date`),
  KEY `idx_type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='交易记录表';

-- ----------------------------
-- Records of transaction_record
-- ----------------------------
BEGIN;
INSERT INTO `transaction_record` (`id`, `user_id`, `type`, `order_number`, `product_name`, `quantity`, `unit`, `amount`, `transaction_date`, `note`, `create_time`, `update_time`) VALUES (23, 16, 'income', '20250309220609685878', '软香粳大米', 100.00, '', 600.00, '2025-03-09 22:06:21', '销售软香粳大米收入', '2025-03-09 22:06:21', '2025-03-09 22:06:21');
INSERT INTO `transaction_record` (`id`, `user_id`, `type`, `order_number`, `product_name`, `quantity`, `unit`, `amount`, `transaction_date`, `note`, `create_time`, `update_time`) VALUES (24, 17, 'income', '20250309222017585600', '高氮复合肥', 1.00, '', 2000.00, '2025-03-09 22:20:41', '销售高氮复合肥收入', '2025-03-09 22:20:41', '2025-03-09 22:20:41');
INSERT INTO `transaction_record` (`id`, `user_id`, `type`, `order_number`, `product_name`, `quantity`, `unit`, `amount`, `transaction_date`, `note`, `create_time`, `update_time`) VALUES (25, 16, 'income', '20250309222358353884', '有机番茄', 10.00, '', 80.00, '2025-03-09 22:24:07', '销售有机番茄收入', '2025-03-09 22:24:07', '2025-03-09 22:24:07');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
